import mysql.connector

from PySide2.QtCore import QObject, QThread
from PySide2.QtCore import Signal, Slot

class MySQLFuncSignals(QObject):
    connectFinished = Signal()
    getPageFinished = Signal()
    getPageSplited = Signal(object)
    executedSQLFinished = Signal(object)

class MySQLFunc(QObject):
    def __init__(self):
        super(MySQLFunc, self).__init__()
        self.signals = MySQLFuncSignals()

    @Slot(object)
    def connect(self, para):
        print(para)
        self.__mysql_conn = mysql.connector.connect(
            user=para["user"], 
            password=para["password"], 
            database=para["database"],
            host=para["host"],
            port=para["port"])
        self.signals.connectFinished.emit()

    @Slot()
    def disconnect(self):
        self.__mysql_conn.close()
        self.signals.connectFinished.emit()

    def buildSqlStr(self, colnames, tblname, limit, offset, filter=''):
        # print("build")
        if len(filter):
            filterstr = " Where " + filter
        else :
            filterstr = ''
        sqlstr = "SELECT " + ",".join(colnames) + \
            " From " + tblname + \
            filterstr + \
            " Limit " + str(limit) + \
            " Offset " + str(offset)
        print (sqlstr)
        return sqlstr

    @Slot(object)
    def getPage(self, para):
        # splits = {}
        # if (para["limit"]>1024):
        #     for i in range(0, piara["limit"], 1024):
        #         splits.append({"limit":para["limit"], "offset": para["offset"]})
        # else:
        #     splits = [{"limit":para["limit"], "offset": para["offset"]}]
        # limit = 512
        # print (para)
        index = 0
        total = int(para["limit"] / para["pagelimited"])
        for i in range(0, para["limit"], para["pagelimited"]):
            # print (i, limit)
            # print(para)
            sqlstr = self.buildSqlStr( \
                para["colnames"], \
                para["tblname"], \
                para["pagelimited"], \
                para["offset"]+i, \
                para["filter"])
            print(sqlstr)
            cursor = self.__mysql_conn.cursor()
            cursor.execute(sqlstr)
            rtn = cursor.fetchall()
            # # print (rtn)
            self.signals.getPageSplited.emit({ \
                "total": total, \
                "index":index, \
                "split": para["pagelimited"], \
                "data": rtn})
            index += 1
        cursor.close()
        self.signals.getPageFinished.emit()

    def executeSQL(self, sqlstr):
        print(sqlstr)
        cursor = self.__mysql_conn.cursor()
        cursor.execute(sqlstr)
        rtn = cursor.fetchall()
        # print(rtn)
        self.signals.executedSQLFinished.emit(rtn)
        cursor.close()

class _MySQLSignals(QObject):
    connecting = Signal(object)
    disconnecting = Signal()
    gettingPage = Signal(object)
    executingSQL = Signal(str)

class MySQLSignals(QObject):
    connectFinished = Signal()
    getPageStarted = Signal()
    getPageFinished = Signal()
    getPageSplited = Signal(object)
    executedSQLFinished = Signal(object)


class MySQL(QObject):    
    def __init__(self):
        super(MySQL, self).__init__()
        self._signals = _MySQLSignals()
        self.signals = MySQLSignals()

        self._thread = QThread()
        self._mysql_func = MySQLFunc()
        self._mysql_func.moveToThread(self._thread)
        self._thread.finished.connect(self._mysql_func.deleteLater)
        self._signals.gettingPage.connect(self._mysql_func.getPage)
        self._signals.executingSQL.connect(self._mysql_func.executeSQL)
        self._signals.connecting.connect(self._mysql_func.connect)
        self._signals.disconnecting.connect(self._mysql_func.disconnect)

        self._mysql_func.signals.connectFinished.connect(self.signals.connectFinished)
        self._mysql_func.signals.getPageFinished.connect(self.signals.getPageFinished)
        self._mysql_func.signals.getPageSplited.connect(self.signals.getPageSplited)
        self._mysql_func.signals.executedSQLFinished.connect(self.signals.executedSQLFinished)

        self._thread.start()

    def __del__(self):
        try:
            self._thread.quit()
            self._thread.wait()
        except:
            pass

    @Slot(object)
    def connect(self, o):
        # print (o)
        self._signals.connecting.emit(o)

    @Slot()
    def disconnect(self):
        self._signals.disconnecting.emit()

    @Slot(object)
    def getPage(self, o):
        # print(o)
        self.signals.getPageStarted.emit()
        self._signals.gettingPage.emit(o)

    @Slot(str)
    def executeSQL(self, sqlstr):
        self._signals.executingSQL.emit(sqlstr)